package com.projectName.www.dao;

import com.projectName.www.util.JDBCUtils;
import com.projectName.www.po.Dish;
import com.projectName.www.po.User;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class DishDaoImpl implements DishDao {
    User user = null;

    Connection connection = null;
    PreparedStatement preparedStatement = null;
    ResultSet resultSet = null;

    @Override
    public void insert(Dish dish) {
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            //写出sql语句
            String sql = "INSERT INTO dish (dId,dTime,dName,dNum,dSum,dPrice,dImg,typeid,slrid) VALUES (null,?,?,?,?,?,?,?,?)";

            preparedStatement = connection.prepareStatement(sql);//执行sql语句


            preparedStatement.setTimestamp(1,dish.getdTime());//为sql语句中的变量赋值
            preparedStatement.setString(2,dish.getDame());//为sql语句中的变量赋值
            preparedStatement.setInt(3,dish.getdNum());//为sql语句中的变量赋值
            preparedStatement.setInt(4,dish.getdSum());//为sql语句中的变量赋值
            preparedStatement.setBigDecimal(5,dish.getdPrice());//为sql语句中的变量赋值
            preparedStatement.setString(6,dish.getdImg());//为sql语句中的变量赋值
            preparedStatement.setInt(7,dish.getTypeid());//为sql语句中的变量赋值
            preparedStatement.setInt(8,dish.getSlrid());


            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

    /**
     * 优化代码
     * @param limit
     */
    public List<Dish> findDishBy(int keyy,String limit)
    {
        List<Dish> dishList=new ArrayList<>();
        try {

            //首先连接数据库
            connection = JDBCUtils.getConnection();
            //写出sql语句
            String sql= "select * from dish";
            //


            if(keyy!=0&&limit!=null)
            {
                sql=sql+" WHERE "+limit+" = ?";

                preparedStatement = connection.prepareStatement(sql);//编译执行sql语句

                preparedStatement.setInt(1,keyy);


                //不能这么写不然字段名field会转成string
               /* if("id".equals(limit)){
                    //sql+="WHERE dId = "+key;
                    preparedStatement.setString(1,"dId");
                    preparedStatement.setInt(2,keyy);
                }
                else if("slr".equals(limit)){
                    //sql+="WHERE slrid = "+key;
                    preparedStatement.setString(1,"slrid");
                    preparedStatement.setInt(2,keyy);
                }else if("type".equals(limit)){
                    //sql+="WHERE typeid= "+key;
                    preparedStatement.setString(1,"typeid");
                    preparedStatement.setInt(2,keyy);

                }*/

            }else{
                preparedStatement=connection.prepareStatement(sql);
            }

            resultSet = preparedStatement.executeQuery();//获得查询结果集



            while (resultSet.next()){

                Dish dish = new Dish();

                dish.setdId(resultSet.getInt("dId"));//将数据库中取出来的id字段字符类型转换为整型并赋给dish对象

                dish.setDame(resultSet.getString("dName"));


                dish.setdTime(resultSet.getTimestamp("dTime"));

                dish.setdNum(resultSet.getInt("dNum"));

                dish.setdSum(resultSet.getInt("dSum"));

                dish.setdPrice(resultSet.getBigDecimal("dPrice"));

                dish.setdImg(resultSet.getString("dImg"));

                dish.setTypeid(resultSet.getInt("typeid"));

                dish.setSlrid(resultSet.getInt("slrid"));
                dishList.add(dish);

            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return dishList;
    }

    @Override
    public List<Dish> findall() {
        List<Dish>dishList=new ArrayList<>();
        DishDaoImpl find=new DishDaoImpl();
        dishList=find.findDishBy(0,null);
        return dishList;
    }

    @Override
    public Dish findByID(String did) {

        DishDaoImpl find=new DishDaoImpl();
        List<Dish>dishList=find.findDishBy(Integer.parseInt(did),"dId");
        Dish dish = dishList.get(0);
        return dish;
    }

    @Override
    public void delete(String did) {

        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "DELETE FROM dish WHERE dId=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,did);//为sql语句中的变量赋值

            preparedStatement.executeUpdate();//操作

        } catch (SQLException e) {
            e.printStackTrace();
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }

    @Override
    public List<Dish> findBySlr(String uID) {

        DishDaoImpl find=new DishDaoImpl();
        List<Dish>dishList=find.findDishBy(Integer.parseInt(uID),"slrid");
        return dishList;
    }

    /**
     * 传过来一个种类名，返回此类下所有菜品
     * @param typename
     */
    @Override
    public List<Dish> selectByType(String typename) {
        //先通过名称找对应id
        DishDaoImpl find=new DishDaoImpl();
        List<Dish>dishList=new ArrayList<>();

        try {

            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select vid from dishcatagory WHERE vName=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,typename);


            resultSet = preparedStatement.executeQuery();//获得查询结果集


            int vid=0;
            if (resultSet.next()){

                vid = resultSet.getInt("vid");
            }


            //查询菜品
            dishList = find.findDishBy(vid, "typeid");


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
        return dishList;
    }

    @Override
    public List<Dish> findDishByName(String dishName) {
        List<Dish> dishList=new ArrayList<>();
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            String sql = "select * from dish where dName like ?";//写出sql语句


            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            dishName='%'+dishName+'%';//模糊查询

            preparedStatement.setString(1,dishName);//为sql语句中的变量赋值

            resultSet = preparedStatement.executeQuery();//获得查询结果集

            while (resultSet.next()){

                Dish dish= new Dish();


                dish.setdId(resultSet.getInt("dId"));//将数据库中取出来的id字段字符类型转换为整型并赋给dish对象

                dish.setDame(resultSet.getString("dName"));


                dish.setdTime(resultSet.getTimestamp("dTime"));

                dish.setdNum(resultSet.getInt("dNum"));

                dish.setdSum(resultSet.getInt("dSum"));

                dish.setdPrice(resultSet.getBigDecimal("dPrice"));

                dish.setdImg(resultSet.getString("dImg"));

                dish.setTypeid(resultSet.getInt("typeid"));

                dishList.add(dish);
            }

        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }



        return dishList;
    }

    @Override
    public void update(Dish dish) {
        try {
            connection = JDBCUtils.getConnection();//首先连接数据库

            //改变姓名，联系方式，地址，简介
                /*
               tolPrice=?,status=?

                 */
            String sql = "update dish set dName=?,dNum=?" +
                    ",dPrice=?" +
                    ",dImg=?" +
                    ",typeid=?" +
                    ",slrid=?" +
                    "where id=?";//写出sql语句

            preparedStatement = connection.prepareStatement(sql);//执行sql语句

            preparedStatement.setString(1,dish.getDame());//为sql语句中的变量赋值
            preparedStatement.setInt(2,dish.getdNum());//为sql语句中的变量赋值
            preparedStatement.setBigDecimal(3,dish.getdPrice());//为sql语句中的变量赋值
            preparedStatement.setString(4,dish.getdImg());//为sql语句中的变量赋值
            preparedStatement.setInt(5,dish.getTypeid());//为sql语句中的变量赋值
            preparedStatement.setInt(6,dish.getSlrid());//为sql语句中的变量赋值
            preparedStatement.setInt(7,dish.getdId());//为sql语句中的变量赋值

            preparedStatement.executeUpdate();


        } catch (SQLException e) {
            throw new DaoException(e.getMessage(),e);
        } finally {
            JDBCUtils.close(resultSet,preparedStatement,connection);
        }
    }
}
